<%@ page contentType="text/xml"%>
<%
	response.setContentType("text/xml");
%>
<%-- Import the java.sql package --%>
<%@ page import="java.sql.*"%>
<%-- -------- Open Connection Code -------- --%>
<application> 
<%
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	int studentId = 0;
	String res = null;
	String cit = null;
	String spec2 = null;
	String name = null;
	String city = null;
	String phoneno = null;
	String discip = null;
	String uni = null;
	String date = null;
	String loc = null;
	try {
		// Registering Postgresql JDBC driver with the DriverManager
		Class.forName("org.postgresql.Driver");

		// Open a connection to the database using DriverManager
		conn = DriverManager
				.getConnection("jdbc:postgresql://localhost/admissions?"
						+ "user=postgres&password=27428572");
%>
<%
	// Begin transaction
		//insert Address
		conn.setAutoCommit(false);
		String userid = request.getParameter("id");
		Statement stmt3 = conn.createStatement();
		ResultSet rs3 = stmt3
				.executeQuery("SELECT student_id FROM user_link WHERE user_id='"
						+ userid + "'");
		rs3.next();
		studentId = rs3.getInt(1);
		rs3 = stmt3
				.executeQuery("SELECT name,address,spec,residence,citizenship FROM student WHERE id='"
						+ studentId + "'");
		rs3.next();
		name = rs3.getString(1);
		int addId = rs3.getInt(2);
		int specId = rs3.getInt(3);
		int resId = rs3.getInt(4);
		int citId = rs3.getInt(5);
		rs3 = stmt3
				.executeQuery("SELECT name FROM countries WHERE id='"
						+ resId + "'");
		rs3.next();
		res = rs3.getString(1);
		rs3 = stmt3
				.executeQuery("SELECT name FROM countries WHERE id='"
						+ citId + "'");
		rs3.next();
		cit = rs3.getString(1);
		rs3 = stmt3
				.executeQuery("SELECT name FROM specializations WHERE id='"
						+ specId + "'");
		rs3.next();
		spec2 = rs3.getString(1);
%>
<%
	if (res.equals("United States")) {
			rs3 = stmt3
					.executeQuery("SELECT street,city,areacode,zipcode,phoneno,state FROM addresses WHERE id='"
							+ addId + "'");
			rs3.next();
			String street = rs3.getString(1);
			city = rs3.getString(2);
			int areacode = rs3.getInt(3);
			int zipcode = rs3.getInt(4);
			phoneno = Integer.toString(rs3.getInt(5));
			int stateId = rs3.getInt(6);
			rs3 = stmt3
					.executeQuery("SELECT name FROM states WHERE id='"
							+ stateId + "'");
			rs3.next();
			String state = rs3.getString(1);
%>
<address>Address : </address>
<street><%=street%> </street>
<city><%=city%> </city>
<state><%=state%></state>
<telecode>.</telecode>
<areacode>AreaCode : <%=areacode%> </areacode>
<zipcode>ZipCode : <%=zipcode%></zipcode>
<phoneno>Phone Number : <%=phoneno%></phoneno>
<%
	} else {
			rs3 = stmt3
					.executeQuery("SELECT street,city,areacode,zipcode,phoneno,telecode FROM addresses WHERE id='"
							+ addId + "'");
			rs3.next();
			String street = rs3.getString(1);
			city = rs3.getString(2);
			int areacode = rs3.getInt(3);
			int zipcode = rs3.getInt(4);
			phoneno = Integer.toString(rs3.getInt(5));
			int telecode = rs3.getInt(6);
%>
<address>Address : </address>
<street><%=street%> </street>

<state>.</state>
<telecode>TeleCode : <%=telecode%> </telecode>
<areacode>AreaCode : <%=areacode%> </areacode>
<zipcode>ZipCode : <%=zipcode%></zipcode>
<%
rs3 = stmt3.executeQuery("SELECT name,location,university, gpa,date,title FROM degrees WHERE student='"
									+ studentId + "'");
			if (rs3.next()) {
				int discipId = rs3.getInt(1);
				int locationId = rs3.getInt(2);
				int uniId = rs3.getInt(3);
				int gpa = rs3.getInt(4);
				date = Integer.toString(rs3.getInt(5));
				Statement stmt4= conn.createStatement();
				ResultSet rs4 = stmt4
						.executeQuery("SELECT name FROM disciplines WHERE id='"
								+ discipId + "'");
				rs4.next();
				discip = rs4.getString(1);
				rs4 = stmt4.executeQuery("SELECT name FROM countries WHERE id='"
								+ locationId + "'");
				rs4.next();
				loc = rs4.getString(1);
				rs4 = stmt4.executeQuery("SELECT name FROM universities WHERE id='"
								+ locationId + "'");
				rs4.next();
				uni = rs4.getString(1);
				%>
				
				
				<%
			}
			%>


<%
	}

		conn.setAutoCommit(true);

	}

	catch (SQLException e) {
		throw new RuntimeException(e);
	}

	finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
			}
			rs = null;
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
			}
			pstmt = null;
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
			}
			conn = null;
		}

	}
%>
<discip>Discipline : <%=discip%></discip>
<uni>Universities : <%=uni%></uni>
<loc>Location :<%=loc%></loc>
<date>Award Date : <%=date%></date>
<city><%=city%> </city>
<phoneno>Phone Number : <%=phoneno%></phoneno>
<userid><%=studentId%> : </userid> 
<name><%=name%></name>
<res>Residence : <%=res%></res> 
<cit>Citizenship : <%=cit%></cit> 
<spec>Specialization : <%=spec2%></spec> 
</application>
